"""
通知相关的数据库操作函数
"""

from .db import get_db_connection, close_connection

async def get_notifications(user_id, current_page=1, page_size=10):
    """
    获取用户通知列表，带分页功能
    
    Args:
        user_id: 用户ID
        current_page: 当前页码，从1开始
        page_size: 每页显示条数
    
    Returns:
        dict: 包含通知列表和总记录数的字典
    """
    connection = None
    try:
        connection = await get_db_connection()
        async with connection.cursor() as cursor:
            # 计算总记录数
            count_sql = """
                SELECT COUNT(*) as total
                FROM notifications 
                WHERE user_id = %s
            """
            await cursor.execute(count_sql, (user_id,))
            total = (await cursor.fetchone())['total']

            # 获取分页数据
            offset = (current_page - 1) * page_size
            sql = """
                SELECT id, message, created_at, is_warning 
                FROM notifications 
                WHERE user_id = %s 
                ORDER BY created_at DESC
                LIMIT %s OFFSET %s
            """
            await cursor.execute(sql, (user_id, page_size, offset))
            notifications = await cursor.fetchall()
            
            return {
                'data': [dict(notification) for notification in notifications],
                'total': total
            }
    except Exception as e:
        print(f"获取通知失败: {str(e)}")
        return {'data': [], 'total': 0}
    finally:
        await close_connection(connection)

async def send_family_message(user_id, message):
    """
    发送家庭消息
    
    Args:
        user_id: 发送者用户ID
        message: 消息内容
    
    Returns:
        bool: 是否发送成功
    """
    connection = None
    try:
        connection = await get_db_connection()
        async with connection.cursor() as cursor:
            # 首先获取发送者的家庭类型
            sql = "SELECT family_type FROM users WHERE id = %s"
            await cursor.execute(sql, (user_id,))
            sender = await cursor.fetchone()
            if not sender:
                return False

            # 获取同一家庭的所有成员
            sql = "SELECT id, username FROM users WHERE family_type = %s"
            await cursor.execute(sql, (sender['family_type'],))
            family_members = await cursor.fetchall()

            # 获取发送者用户名
            sql = "SELECT username FROM users WHERE id = %s"
            await cursor.execute(sql, (user_id,))
            sender_info = await cursor.fetchone()
            sender_name = sender_info['username'] if sender_info else '未知用户'

            # 为每个家庭成员插入一条消息记录
            message_with_sender = f"{sender_name}: {message}"
            for member in family_members:
                sql = """
                    INSERT INTO notifications (user_id, message, created_at, is_warning)
                    VALUES (%s, %s, NOW(), FALSE)
                """
                await cursor.execute(sql, (member['id'], message_with_sender))
            
            await connection.commit()
            return True
    except Exception as e:
        print(f"发送家庭消息失败: {str(e)}")
        return False
    finally:
        await close_connection(connection)

async def send_system_notification(user_id, message, is_warning=0, sender_id=None):
    """
    发送系统通知
    
    Args:
        user_id: 接收者用户ID
        message: 消息内容
        is_warning: 消息类型（0:普通 1:警告/拒绝 2:成功/接受）
        sender_id: 发送者ID
    
    Returns:
        bool: 是否发送成功
    """
    connection = None
    try:
        connection = await get_db_connection()
        async with connection.cursor() as cursor:
            # 开始事务
            await connection.begin()
            try:
                sql = """
                    INSERT INTO notifications (user_id, message, created_at, is_warning, sender_id)
                    VALUES (%s, %s, NOW(), %s, %s)
                """
                await cursor.execute(sql, (user_id, message, is_warning, sender_id))
                await connection.commit()
                return True
            except Exception as e:
                await connection.rollback()
                print(f"发送系统通知事务执行失败: {str(e)}")
                return False
    except Exception as e:
        print(f"发送系统通知失败: {str(e)}")
        return False
    finally:
        if connection:
            await close_connection(connection)

async def get_user_message_status(user_id):
    """
    获取用户消息状态
    
    Args:
        user_id: 用户ID
    
    Returns:
        dict: 用户消息状态信息
    """
    connection = None
    try:
        connection = await get_db_connection()
        async with connection.cursor() as cursor:
            sql = "SELECT last_message_count, current_message_count FROM user_message_status WHERE user_id = %s"
            await cursor.execute(sql, (user_id,))
            status = await cursor.fetchone()
            return dict(status)
    except Exception as e:
        print(f"获取用户消息状态失败: {str(e)}")
        return None
    finally:
        await close_connection(connection)

async def update_last_message_count(user_id, current_count):
    """
    更新用户最后消息数
    
    Args:
        user_id: 用户ID
        current_count: 当前消息数
    """
    connection = None
    try:
        connection = await get_db_connection()
        async with connection.cursor() as cursor:
            sql = "INSERT INTO user_login_info (user_id, last_message_count) VALUES (%s, %s) ON DUPLICATE KEY UPDATE last_message_count = %s"
            await cursor.execute(sql, (user_id, current_count, current_count))
            await connection.commit()
    except Exception as e:
        print(f"更新消息数失败: {str(e)}")
    finally:
        await close_connection(connection)

async def handle_invite(notification_id, user_id, accept):
    """
    处理家庭邀请
    
    Args:
        notification_id: 通知ID
        user_id: 用户ID
        accept: 是否接受邀请
    
    Returns:
        bool: 处理是否成功
    """
    connection = None
    try:
        connection = await get_db_connection()
        async with connection.cursor() as cursor:
            # 获取通知信息
            await cursor.execute(
                """
                SELECT n.*, u.username as sender_name, u.family_type as family_type 
                FROM notifications n
                LEFT JOIN users u ON n.sender_id = u.id
                WHERE n.id = %s AND n.user_id = %s
                """,
                [notification_id, user_id]
            )
            notification = await cursor.fetchone()
            if not notification:
                return False

            # 更新通知状态
            await cursor.execute(
                "UPDATE notifications SET is_warning = %s WHERE id = %s",
                [2 if accept else 1, notification_id]
            )

            # 发送回执通知给邀请者
            if notification['sender_id']:
                await cursor.execute(
                    "SELECT username FROM users WHERE id = %s",
                    [user_id]
                )
                user = await cursor.fetchone()
                if user:
                    response_message = f"系统通知：{user['username']}{'接受' if accept else '拒绝'}了你的家庭邀请"
                    await send_system_notification(
                        notification['sender_id'],
                        response_message,
                        is_warning=2 if accept else 1
                    )

            # 如果接受邀请，更新用户的家庭信息
            if accept:
                await cursor.execute(
                    """
                    UPDATE users 
                    SET family_type = %s, family_permissions = 2
                    WHERE id = %s
                    """,
                    [notification['family_type'], user_id]
                )

            await connection.commit()
            return True
    except Exception as e:
        print(f"处理邀请失败: {str(e)}")
        return False
    finally:
        await close_connection(connection)

async def handle_join(notification_id, user_id, accept):
    """
    处理加入家庭申请
    
    Args:
        notification_id: 通知ID
        user_id: 用户ID
        accept: 是否接受申请
    
    Returns:
        bool: 处理是否成功
    """
    connection = None
    try:
        connection = await get_db_connection()
        async with connection.cursor() as cursor:
            # 获取通知信息
            await cursor.execute(
                """
                SELECT n.*, u.username as sender_name, u.family_type as family_type 
                FROM notifications n
                LEFT JOIN users u ON n.sender_id = u.id
                WHERE n.id = %s AND n.user_id = %s
                """,
                [notification_id, user_id]
            )
            notification = await cursor.fetchone()
            if not notification:
                return False

            # 更新通知状态
            await cursor.execute(
                "UPDATE notifications SET is_warning = %s WHERE id = %s",
                [2 if accept else 1, notification_id]
            )

            # 发送回执通知给申请者
            if notification['sender_id']:
                await cursor.execute(
                    "SELECT username,family_type FROM users WHERE id = %s",
                    [user_id]
                )
                user = await cursor.fetchone()
                if user:
                    response_message = f"系统通知：{user['username']}{'接受' if accept else '拒绝'}了你的家庭申请"
                    await send_system_notification(
                        notification['sender_id'],
                        response_message,
                        is_warning=2 if accept else 1
                    )

            # 如果接受申请，更新用户的家庭信息
            if accept:
                await cursor.execute(
                    """
                    UPDATE users 
                    SET family_type = %s, family_permissions = 2
                    WHERE id = %s
                    """,
                    [user['family_type'], notification['sender_id']]
                )
            await connection.commit()
            return True
    except Exception as e:
        print(f"处理邀请失败: {str(e)}")
        return False
    finally:
        await close_connection(connection)





